/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.servientrega.dao;

import com.servientrega.service.Shipment;
import com.servientrega.service.ShipmentItem;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class ServientregaDAO {

    private static String CREATE_SHIPMENT = "{ call PKG_KALLSONYS.CREATE_SHIPMENT (?, ?, ?, ?, ?, ?, ?, ?) }";
    private static String CREATE_SHIPMENT_ITEMS = "{ call PKG_KALLSONYS.CREATE_SHIPMENT_ITEMS (?, ?, ?, ?, ?, ?) }";
    private static String GET_SHIPMENT_STATUS = "{ call PKG_KALLSONYS.GET_SHIPMENT_STATUS (?, ?) }";
    private DataSource ds;

    public ServientregaDAO(DataSource ds) {
        this.ds = ds;
    }

    /**
     * CreateShipment
     * @param shipment
     * @return boolean
     * @throws Exception 
     */
    public boolean CreateShipment(Shipment shipment) throws Exception {
        boolean retorno = false;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_SHIPMENT);

            stmt.setLong(1, shipment.getOrderId());
            stmt.setString(2, shipment.getFirstName());
            stmt.setString(3, shipment.getLastName());
            stmt.setString(4, shipment.getStreet());
            stmt.setString(5, shipment.getCity());
            stmt.setString(6, shipment.getState());
            stmt.setString(7, shipment.getZipCode());
            stmt.setString(8, "6");
            //stmt.registerOutParameter(7, OracleTypes.INTEGER);
            stmt.execute();

            if (shipment.getItems() != null && shipment.getItems().size() > 0) {
                for (ShipmentItem item : shipment.getItems()) {
                    stmt = con.prepareCall(CREATE_SHIPMENT_ITEMS);
                    stmt.setLong(1, item.getProductId());
                    stmt.setString(2, item.getProductName());
                    stmt.setString(3, item.getPartNumber());
                    stmt.setLong(4, item.getPrice());
                    stmt.setInt(5, item.getQuantity());
                    stmt.setLong(6, shipment.getOrderId());
                    stmt.execute();
                }
            }
            
            retorno = true; 
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
    
    
    public String getShipmenStatus(long idOrder) throws Exception {
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        String retorno = "-1";

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_SHIPMENT_STATUS);
            stmt.setLong(1, idOrder);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();
           
            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;
                if (rs.next()) {
                   retorno = rs.getString("STATUS");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
}
